#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# https://www.cnblogs.com/iwangzc/p/4112078.html
# https://www.cnblogs.com/chenxi67/p/10376617.html
import traceback

from flask import Flask
from sqlalchemy.sql.schema import Index
from sqlalchemy import create_engine
from sqlalchemy import func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
import time
from sqlalchemy.ext.automap import automap_base
from sqlalchemy import MetaData
from typing import Optional


# 创建对象的基类:
from app.business.config import config_base

Base = declarative_base()
app = Flask(__name__)

MysqlEngineStart = False
MysqlEngine = None
MysqlGlobalSession = None


def get_engine_pool():
    global engine_pool
    if not engine_pool:

        engine_pool = create_engine(
            config_base.local_host_mysql,
            # SQLALCHEMY_DATABASE_URI_,
            pool_size=200,
            max_overflow=5,
            pool_timeout=50,
            pool_pre_ping=True,
            pool_recycle=1800)
        time.sleep(2)
    return engine_pool


engine_pool = None


class MysqlClient:

    def __init__(self):
        global MysqlEngineStart
        self.engine_pool = get_engine_pool()
        if not MysqlEngineStart:
            global MysqlGlobalSession
            MysqlEngineStart = True
            # 初始化数据库连接:
            Base.metadata.bind = self.engine_pool
            session_factory = sessionmaker(bind=self.engine_pool, autocommit=True, autoflush=True,
                                           expire_on_commit=False)
            self.session = scoped_session(session_factory)
            MysqlGlobalSession = self.session

    @staticmethod
    def set_table_name(table_object, month: Optional[str]):
        table = table_object()
        table.__table__.name = table.__tablename__ % month
        return table

    def get_session(self):
        Base.metadata.bind = self.engine_pool
        session_factory = sessionmaker(bind=self.engine_pool, autocommit=True, autoflush=True,
                                       expire_on_commit=False)
        session = scoped_session(session_factory)
        return session()

    def get_session2(self):
        Base.metadata.bind = self.engine_pool
        session_factory = sessionmaker(bind=self.engine_pool, autocommit=False, autoflush=True,
                                       expire_on_commit=False)
        session = scoped_session(session_factory)
        return session()

    @staticmethod
    def add(obj):
        if not MysqlGlobalSession:
            MysqlClient.init()
        error = None
        for i in range(5):
            session = MysqlGlobalSession()
            try:
                session.add(obj)
                session.flush()
                return obj.id
            except Exception as e:
                if "IntegrityError" in str(e):
                    try:
                        return update_duplicate(obj)
                    except Exception as e:
                        app.logger.error(e)
                else:
                    app.logger.error(e)
                    error = e
            finally:
                session.close()
        if error:
            raise Exception(error)

    @staticmethod
    def add_all_data(objs):
        print(objs)
        if not MysqlGlobalSession:
            MysqlClient.init()
        error = None
        for i in range(5):
            session = MysqlGlobalSession()
            try:
                print(objs)
                session.add_all(objs)
                session.flush()
            except Exception as e:
                if "IntegrityError" in str(e):
                    try:
                        return update_duplicate(objs)
                    except Exception as e:
                        app.logger.error(e)
                else:
                    app.logger.error(e)
                    error = e
            finally:
                session.close()
        if error:
            raise Exception(error)

    @staticmethod
    def first_add(obj):
        if "sqlalchemy.ext.automap" not in str(type(obj)):
            old_obj = obj
            bind = DbBind(obj)
            obj = bind.get_add_data_model()
            for (key, value) in old_obj.__dict__.items():
                if not key.startswith("_"):
                    setattr(obj, key, value)
        if not MysqlGlobalSession:
            MysqlClient.init()
        session = MysqlGlobalSession()
        try:
            session.add(obj)
            session.flush()
            return obj.id
        except Exception as e:
            app.logger.error(e)
        finally:
            session.close()
        return None

    @staticmethod
    def init():
        global MysqlEngineStart
        if not MysqlEngineStart or not MysqlGlobalSession:
            MysqlEngineStart = False
            MysqlClient()
            time.sleep(0.1)


    @staticmethod
    def sql_query_all(sql, **params) -> Optional[list]:
        with MysqlSession() as session:
            error = None
            for i in range(5):
                try:
                    result = session.execute(sql, params=params).fetchall()
                    return result
                except Exception as e:
                    app.logger.info("sql_execute {} {}".format(sql, repr(e)))
                    error = e
            if error:
                raise Exception(error)

    @staticmethod
    def sql_query_one(sql, **params) -> Optional[tuple]:
        with MysqlSession() as session:
            error = None
            for i in range(5):
                try:
                    result = session.execute(sql, params=params).fetchone()
                    return result
                except Exception as e:
                    app.logger.info("sql_execute {} {}".format(sql, repr(e)))
                    error = e
            if error:
                raise Exception(error)

    @staticmethod
    def count(obj) -> Optional[int]:
        MysqlClient.init()
        obj = DbBind(obj).get_query_model()
        return MysqlGlobalSession().query(func.count('*')).select_from(obj).scalar()

    @staticmethod
    def sql_execute(sql, **params):
        with MysqlSession() as session:
            error = None
            for i in range(5):
                try:
                    if params:
                        result = session.execute(sql, params=params)
                    else:
                        result = session.execute(sql)
                    return result
                except Exception as e:
                    app.logger.info("sql_execute {} {}".format(sql, repr(e)))
                    error = e
            session.commit()
            if error:
                raise Exception(error)


def update_duplicate(obj):
    session = MysqlClient.get_session()
    try:
        try:
            new_obj = eval(obj.__class__.__name__)
        except Exception as e:
            new_obj = eval(obj.__table_name__)
        new_obj.__table__.name = obj.__table__.name
        update_object = DbBind(new_obj).get_query_model()
        params = {}
        for args in new_obj.__table_args__:
            if isinstance(args, Index) and args.unique:
                for k in args.columns.keys():
                    params[k] = eval("obj.{}".format(k))
        first = session.query(update_object).filter_by(**params).first()
        f_id = first.id
        for (key, value) in obj.__dict__.items():
            if not key.startswith("_"):
                setattr(first, key, value)
        first.id = f_id
        session.commit()
    except Exception as e:
        app.logger.info(traceback.format_exc())
        app.logger.error("update_duplicate {}".format(repr(e)))
    finally:
        session.close()


def to_dict(self):
    return {c.name: getattr(self, c.name) for c in self.__table__.columns}


ALL_BIND = {}


class DbBind(object):
    def __init__(self, table_model, year: Optional[str] = ""):
        if year:
            table_name = table_model.__tablename__ % year
        else:
            table_name = table_model.__table__.name
        try:
            self.table_name_model_name = table_model.__name__
        except Exception as e:
            self.table_name_model_name = table_model.__class__.__name__
        map_tables = [table_name]
        self.table_name = table_name
        if ALL_BIND.get(table_name):
            return
        self.tables = map_tables
        self.metadata = MetaData()
        self.metadata.reflect(engine_pool, only=map_tables)
        self.auto_base = automap_base(metadata=self.metadata)
        self.auto_base.prepare()
        ALL_BIND[table_name] = self.auto_base

    def get_query_model(self):
        try:
            model = getattr(ALL_BIND.get(self.table_name).classes, self.table_name)
            model.to_dict = to_dict
            return model
        except AttributeError:
            raise

    def get_add_data_model(self):
        try:
            model = getattr(ALL_BIND.get(self.table_name).classes, self.table_name)
            model.__table_name__ = self.table_name_model_name
            model.to_dict = to_dict
            return model()
        except AttributeError:
            raise

    def get_add_model(self):
        try:
            model = getattr(ALL_BIND.get(self.table_name).classes, self.table_name)
            model.__table_name__ = self.table_name_model_name
            model.to_dict = to_dict
            return model()
        except AttributeError:
            raise


class MysqlSession:
    def __init__(self):
        MysqlClient.init()
        self.session = MysqlGlobalSession()

    def __enter__(self):
        return self.session

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.session.close()
